﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Meqs.Entity;
using Meqs.Logic.Core;

namespace Meqs.Logic.Implement.MsSql
{
    public class EvalSettingsLogic : AbEvalSettingsLogic
    {
        private static IDBAccess _dbAccess;
        /// <summary>
        /// 查询评价分配明细
        /// </summary>
        /// <param name="causeID"></param>
        /// <param name="evalType"><see cref="EvalType"/></param>
        /// <param name="schoolMark"></param>
        /// <returns></returns>
        public override DataTable GetSettingsDetail(int causeID, EvalType evalType, string schoolMark)
        {
            _dbAccess = DBAccessFactory.CreateInstance(DBType.MsSQL);
            try
            {
                _dbAccess.Init(ConnectionString);
                _dbAccess.Open();
                string sql = "select rid, evalname from " + schoolMark + ".evalitems where remarks = '" + (int)evalType + "' order by rid";
                DataTable dt = _dbAccess.RunQuery(sql);
                if (dt == null || dt.Rows.Count == 0) return null;
                sql = "select ";
                foreach (DataRow row in dt.Rows)
                {
                    int itemID = int.Parse(row[0].ToString());
                    string evalName = row[1].ToString();
                    object[] objArray = GetCauseDetail(causeID, itemID, schoolMark);
                    sql += string.Format("'{0}' as [{1}],", objArray[1], evalName);
                }
                sql = sql.TrimEnd(',');
                return _dbAccess.RunQuery(sql);
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                _dbAccess.Close();
            }
        }

        /// <summary>
        /// 获取EvalItems和Cause的详情
        /// </summary>
        /// <param name="causeID"></param>
        /// <param name="itemID"></param>
        /// <param name="schoolMark"></param>
        /// <returns></returns>
        private object[] GetCauseDetail(int causeID, int itemID, string schoolMark)
        {
            object[] objArray = new object[2];
            string sql = @"select destid from {0}.evalsettings 
                where ',' + taskidlist + ',' like '%,{1},%' 
                and ',' + researchidlist + ',' like '%,{2},%'";
            sql = string.Format(sql, schoolMark, itemID, causeID);
            DataTable dt = _dbAccess.RunQuery(sql);
            if (dt == null || dt.Rows.Count == 0) return new object[] { 0, "" };

            string destStr = "";
            foreach (DataRow row in dt.Rows)
            {
                int destId = int.Parse(row[0].ToString());
                sql = @"select TeachCode, TeachName from teacherinfo
                        where rid = " + destId;
                dt = _dbAccess.RunQuery(sql);
                if (dt == null || dt.Rows.Count == 0) continue;
                string teachCode = dt.Rows[0][0].ToString();
                string teachName = dt.Rows[0][1].ToString();
                destStr += string.Format("{0}({1}),", teachCode, teachName);
            }
            destStr = destStr.TrimEnd(',');
            objArray[0] = itemID;
            objArray[1] = destStr;
            return objArray;
        }

        /// <summary>
        /// 查询评价分配明细
        /// </summary>
        /// <param name="gradeIndex"></param>
        /// <param name="schoolMark"></param>
        /// <returns></returns>
        public override DataTable GetSettingsDetailGrade(int gradeIndex, string schoolMark)
        {
            _dbAccess = DBAccessFactory.CreateInstance(DBType.MsSQL);
            try
            {
                _dbAccess.Init(ConnectionString);
                _dbAccess.Open();
                string sql = "select rid, evalname from " + schoolMark + ".evalitems where remarks='1' order by rid";
                DataTable dt = _dbAccess.RunQuery(sql);
                if (dt == null || dt.Rows.Count == 0) return null;
                sql = "select ";
                foreach (DataRow row in dt.Rows)
                {
                    int itemID = int.Parse(row[0].ToString());
                    string evalName = row[1].ToString();
                    object[] objArray = GetGradeDetail(gradeIndex, itemID, schoolMark);
                    sql += string.Format("'{0}' as [{1}],", objArray[1], evalName);
                }
                sql = sql.TrimEnd(',');
                return _dbAccess.RunQuery(sql);
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                _dbAccess.Close();
            }
        }

        /// <summary>
        /// 获取EvalItems和Grade的详情
        /// </summary>
        /// <param name="gradeIndex"></param>
        /// <param name="itemID"></param>
        /// <param name="schoolMark"></param>
        /// <returns></returns>
        private object[] GetGradeDetail(int gradeIndex, int itemID, string schoolMark)
        {
            object[] objArray = new object[2];
            string sql = @"select destid from {0}.evalsettings 
                where ',' + taskidlist + ',' like '%,{1},%' 
                and ',' + gradelist + ',' like '%,{2},%'";
            sql = string.Format(sql, schoolMark, itemID, gradeIndex);
            DataTable dt = _dbAccess.RunQuery(sql);
            if (dt == null || dt.Rows.Count == 0) return new object[] { 0, "" };

            string destStr = "";
            foreach (DataRow row in dt.Rows)
            {
                int destId = int.Parse(row[0].ToString());
                sql = @"select TeachCode, TeachName from teacherinfo
                        where rid = " + destId;
                dt = _dbAccess.RunQuery(sql);
                if (dt == null || dt.Rows.Count == 0) continue;
                string teachCode = dt.Rows[0][0].ToString();
                string teachName = dt.Rows[0][1].ToString();
                destStr += string.Format("{0}({1}),", teachCode, teachName);
            }
            destStr = destStr.TrimEnd(',');
            objArray[0] = itemID;
            objArray[1] = destStr;
            return objArray;
        }
    }
}
